Generated code - using GROUP BY and HAVING clauses, SelfServicing
Preface
This section discusses the usage of GROUP BY and HAVING clauses with typed lists, typed views and dynamic lists. Usage is
the same for all three.
Using GroupByCollection and Having Clauses
Data in lists, be it a
TypedView, a
TypedList or a
Dynamic
List, is often grouped into smaller sets, which are then processed by
Aggregate functions. LLBLGen Pro allows you to specify a
GroupByCollection when calling a TypedList's or TypedView's
Fill()
method. The
GroupByCollection can contain a custom filter which will
be used as a HAVING clause in the query to generate. The filter is a normal
PredicateExpression and can contain any predicate you would otherwise
use in a normal filter, with one restriction: fields referred to in a Having
clause have to be part of the
GroupByCollection or have to have an
aggregate function applied to them. This is a SQL restriction. For more
information about aggregate functions, please see
Field expressions and aggregates.
To make effective use of a GROUP BY action, fields in the resultset should
be in the
GroupByCollection or have an aggregate function applied to
them. You can apply aggregate functions in the
TypedList editor, and
also in your code. The example below applies aggregates and expressions to
the fields in a
TypedList called
OrderTotals. The
TypedList
contains just two fields:
OrderDetails.
OrderId (aliased as
"OrderId" at index 0) and
OrderDetails.
UnitPrice (aliased as "TotalPrice",
as it will contain the total price of the order, at index 1).
By
applying expressions, aggregates and a group by action, together with a
having clause, the typed list will contain all orders with a total price
higher than $1,000.=. The data also could have been read using a dynamic
list, as is illustrated later in this section. By assigning the expression
and aggregate function to the field object which is used in both the fields
list for fetching and the having clause, the expression and aggregate are
used in both the select list and the having clause.
// C#
OrderTotalsTypedList orderTotals = new OrderTotalsTypedList();
IGroupByCollection groupByClause = new GroupByCollection();
// grab the fields collection.
IEntityFields fields = orderTotals.BuildResultset();
groupByClause.Add(fields[0]);
// construct Having filter.
// Expression for total price: ((unitprice * quantity) - ((unitprice * quantity) * discount) )
groupByClause.HavingClause = new PredicateExpression(
fields[1]
.SetExpression(
(OrderDetailsFields.UnitPrice * OrderDetailsFields.Quantity) -
((OrderDetailsFields.UnitPrice * OrderDetailsFields.Quantity) * OrderDetailsFields.Discount))
.SetAggregateFunction(AggregateFunction.Sum)
> 1000.0f);
TypedListDAO dao = new TypedListDAO();
dao.GetMultiAsDataTable(fields, orderTotals, 0, null, null, orderTotals.BuildRelationSet(),
true, groupByClause, null, 0, 0);
' VB.NET
Dim orderTotals As New OrderTotalsTypedList()
' grab the fields collection.
Dim fields As IEntityFields = orderTotals.BuildResultset()
Dim groupByClause As IGroupByCollection = New GroupByCollection()
groupByClause.Add(fields(0))
' construct Having filter.
' Expression for total price: ((unitprice * quantity) - ((unitprice * quantity) * discount) )
groupByClause.HavingClause = New PredicateExpression( _
fields(1) _
.SetExpression( _
(OrderDetailsFields.UnitPrice * OrderDetailsFields.Quantity) - _
((OrderDetailsFields.UnitPrice * OrderDetailsFields.Quantity) * OrderDetailsFields.Discount)) _
.SetAggregateFunction(AggregateFunction.Sum) _
> 1000.0)
Dim dao As New TypedListDAO()
dao.GetMultiAsDataTable(fields, orderTotals, 0, Nothing, Nothing, orderTotals.BuildRelationSet(), _
True, groupByClause, Nothing, 0, 0)
The main part is the creation of the Expression object which will calculate
the proper total for an order. As expression objects are re-usable objects,
the code might look a little verbose, but can be re-used in your
application. The
GroupByCollection's
HavingClause is set to a
FieldCompareValuePredicate object which compares the
TotalPrice
value with a value of 1000.0. As the field used in the predicate is the
same as the field in the resultset, we get the proper expression and
aggregate function applied to the field in the Having clause, as the Total
price has to be re-calculated in the Having clause.